package com.zaofan.dao;

import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.zaofan.bean.Comment;
import com.zaofan.bean.ScoreAve;
import com.zaofan.dao.base.DaoSupportImpl;

public class CommentDao extends DaoSupportImpl<Comment> {

	@SuppressWarnings("unchecked")
	public List<Comment> findByShopId(Integer shopId) {
		try {
			String sql = "select c.id,a.nickName,c.orderId,c.orderNo,c.orderTime,c.accountId,c.accountMobile,c.shopId,c.shopName,c.tasteScore,c.speedScore,c.content,c.ave,c.state,c.commentType,c.addTime,c.isReply,a.photo as userPhoto from comment c , account a where c.shopId="
					+ shopId + " and c.accountId = a.id order by c.addTime desc;";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(Comment.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public ScoreAve avgScore(int shopId) {
		try {
			String sql = "select count(*) count,sum(ave) sum from comment where shopId = " + shopId;
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanHandler<>(ScoreAve.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new ScoreAve();
	}

	public List<Comment> findGoodsComments(int goodsId, int begin, int end) {
		try {
			String sql = "select c.*,a.nickName,a.photo as userPhoto,count(distinct c.orderNo) from comment c, goodsorderdetail o,account a where c.orderNo = o.orderNo and  o.productId = "
					+ goodsId + " and a.id = c.accountId group by c.orderNo order by c.addTime limit " + begin + ","
					+ end;
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(Comment.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	public List<Comment> findProductComments(int goodsId, int begin, int end) {
		try {
			String sql = "select c.*,a.nickName,a.photo as userPhoto,count(distinct c.orderNo) from comment c, orderdetail o,account a where c.orderNo = o.orderNo and  o.productId = "
					+ goodsId + " and a.id = c.accountId group by c.orderNo order by c.addTime limit " + begin + ","
					+ end;
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(Comment.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

}
